package com.blue.waffle.iwc.domains.helpers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.blue.waffle.iwc.domains.Level;
import com.blue.waffle.iwc.domains.Profile;
import com.blue.waffle.iwc.utils.LruCache;

public class ProfileLevelHelper implements DataHelper<Level> {

private Connection connection;
	
	private static final String TABLE_NAME="profile_level";
	private static final String TABLE_LEVEL="level";
	//private static final String TABLE_PROFILE="profile";
	
	private PreparedStatement selectStatement;
	private PreparedStatement updateStatement;
	private PreparedStatement insertStatement;
	private PreparedStatement deleteStatement;
	
	private static final String SELECT_ALL_SQL="SELECT l.*, pl.* FROM "+TABLE_NAME+ " pl INNER JOIN "+TABLE_LEVEL+" l on pl.level_id=l.id";
	private static final String SELECT_ALL_PROFILE_LEVEL_SQL="SELECT l.*, pl.* FROM "+TABLE_NAME+ " pl INNER JOIN "+"TABLE_LEVEL"+" l on pl.level_id=l.id WHERE pl.profile_id=?";
	
	private static LruCache<Integer, Level> objectCache;
	
	private static final String INSERT_SQL="INSERT INTO "+TABLE_NAME+" (profile_id, level_id ,score,playtime,time_completed) VALUES (?,?,?,?,?)";
	private static final String UPDATE_SQL="UPDATE "+TABLE_NAME+" SET score=?, playtime=?, time_completed=? WHERE profile_id=? AND level_id=? AND score<?";
	private static final String DELETE_SQL="DELETE FROM "+TABLE_NAME+" WHERE profile_id=? AND level_id=?";
	private static final String DELETE_BY_PROFILE_SQL="DELETE FROM "+TABLE_NAME+" WHERE profile_id=?";
	
	private static final String SELECT_EXIST_SQL="SELECT 1 FROM "+TABLE_NAME+" WHERE profile_id=? AND level_id=?";
	
	private static final String SELECT_STATISTICS_SQL = "select w.word, e.score,e.playtime, n.score, n.playtime,h.score ,h.playtime from word w inner " +
			"join (select w.id, score, playtime from word w inner join level l on l.word_id=w.id and l.difficulty_id=1 left join profile_level e on e.level_id=l.id) e on e.id=w.id  inner " +
			"join (select w.id, score, playtime from word w inner join level l on l.word_id=w.id and l.difficulty_id=2 left join profile_level e on e.level_id=l.id) n on n.id=w.id inner " +
			"join (select w.id, score, playtime from word w inner join level l on l.word_id=w.id and l.difficulty_id=3 left join profile_level e on e.level_id=l.id) h on h.id=w.id";
	
	public ProfileLevelHelper(Connection connection){
		this.connection=connection;
		if (objectCache==null) {
			objectCache=new LruCache<Integer, Level>(10);
		}
	}
	
	@Override
	public List<Level> selectAll() throws SQLException {
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(SELECT_ALL_SQL);
		List<Level> list = new ArrayList<Level>();
		while(resultSet.next()) {
			list.add(getObject(resultSet));
		}
		resultSet.close();
		return list;
	}
	
	public List<Level> selectAll(Profile profile) throws SQLException {
		selectStatement= connection.prepareStatement(SELECT_ALL_PROFILE_LEVEL_SQL);
		selectStatement.setInt(1, profile.getId());
		ResultSet resultSet = selectStatement.executeQuery();
		List<Level> list = new ArrayList<Level>();
		while(resultSet.next()) {
			Level profileLevel=getObject(resultSet);
			profileLevel.setProfile(profile);
			list.add(profileLevel);
		}
		resultSet.close();
		selectStatement.close();
		return list;
	}
	

	@Override
	public List<Level> select(String sql, Object[] para) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void insert(Level model) throws SQLException {
		insertStatement = connection.prepareStatement(INSERT_SQL);
		insertStatement.setInt(1, model.getProfile().getId());
		insertStatement.setInt(2, model.getId());
		insertStatement.setLong(3, model.getScore());
		insertStatement.setLong(4, model.getPlayTime());
		insertStatement.setLong(5, System.currentTimeMillis());
		insertStatement.executeUpdate();
		Statement statement = connection.createStatement();
		ResultSet keyGenerated = statement.executeQuery("SELECT last_insert_rowid()");
		if(keyGenerated.next()) {
			int id = keyGenerated.getInt(1);
			model.setId(id);
			objectCache.put(id, model);
		}
		keyGenerated.close();
		insertStatement.close();
	}

	@Override
	public void update(Level model) throws SQLException {
		updateStatement = connection.prepareStatement(UPDATE_SQL);
		updateStatement.setLong(1, model.getScore());
		updateStatement.setLong(2, model.getPlayTime());
		updateStatement.setLong(3, System.currentTimeMillis());
		updateStatement.setInt(4, model.getProfile().getId());
		updateStatement.setInt(5, model.getId());
		updateStatement.setLong(6, model.getScore());
		updateStatement.executeUpdate();
		objectCache.put(model.getId(), model);
		updateStatement.close();		
	}

	@Override
	public void delete(Level model) throws SQLException {
		deleteStatement = connection.prepareStatement(DELETE_SQL);
		deleteStatement.setInt(1,model.getProfile().getId());
		deleteStatement.setInt(2, model.getId());
		deleteStatement.executeUpdate();
		objectCache.remove(model.getId());
		deleteStatement.close();
	}
	
	public void delete(Profile profile) throws SQLException {
		deleteStatement = connection.prepareStatement(DELETE_BY_PROFILE_SQL);
		deleteStatement.setInt(1,profile.getId());
		deleteStatement.executeUpdate();
		objectCache.clear();
		deleteStatement.close();
	}

	@Override
	public int count(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int count() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Level getObject(ResultSet resultSet) throws SQLException {
		int id=resultSet.getInt(Level.ID);
		Level model=objectCache.get(id);
		if (model==null) {
			model=new Level();
			model.setId(id);
			model=setupObject(resultSet, model);
			objectCache.put(id, model);
		}else {
			model=setupObject(resultSet, model);	
		}
		return model;
	}
	
	private Level setupObject(ResultSet resultSet, Level model) throws SQLException {
		model.getWord().setId(resultSet.getInt(Level.WORD_ID));
		model.getDifficulty().setId(resultSet.getInt(Level.DIFFICULTY_ID));
		model.setTitle(resultSet.getString(Level.TITLE));
		model.setPlayTime(resultSet.getLong(Level.PLAY_TIME));
		model.setScore(resultSet.getLong(Level.SCORE));
		model.setCompleted(true);
		return model;
	}

	@Override
	public boolean isClosed() throws SQLException {
		if (connection!=null) {
			return connection.isClosed();
		}
		return false;
	}

	@Override
	public void close() throws SQLException {
		if (connection != null) {
			if (!connection.isClosed()) {
				connection.close();
			}
			connection = null;
		}
	}

	@Override
	public void setConnection(Connection connection) {
		this.connection=connection;		
	}
	
	public void save(Level model) throws SQLException {
		selectStatement=connection.prepareStatement(SELECT_EXIST_SQL);
		selectStatement.setInt(1,model.getProfile().getId());
		selectStatement.setInt(2,model.getId());
		ResultSet isExist=selectStatement.executeQuery();
		boolean exist=false;
		if (isExist.next()) {
			exist=isExist.getBoolean(1);
		}
		isExist.close();
		selectStatement.close();
		if (exist) {
			update(model);
		}else {
			insert(model);
		}
	}
	
	public List<String[]> SelectStats() throws SQLException
	{
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(SELECT_STATISTICS_SQL);
		List<String[]> list = new ArrayList<String[]>();
		while(resultSet.next()) {
			list.add(getStringArray(resultSet));
		}
		resultSet.close();
		return list;
	}
	
	public String[] getStringArray(ResultSet resultSet) throws SQLException {
		
		String[] stringArray = new String[7];
		
		stringArray[0] = resultSet.getString(1);
		for(int i=1; i<7 ; i++)
		{
			stringArray[i] = String.valueOf(resultSet.getLong(i+1));
		}
		return stringArray;		
	}

}
